package fu.abc.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import fu.abc.model.Error;

public class ErrorDAO {
	Connection connect = null;
	PreparedStatement pre = null;
	ResultSet result = null;
	Statement state = null;
	
// List Error	
	 public List<Error> listError(String status) throws SQLException{
			
		 List<Error> list = new ArrayList<>();
		 String query ="";
		 
//		 SELECT *
		 String listall = " SELECT id,location,errorname,localname,content,c.[status]"
		 		+ " FROM LOCATION a, REPORT_ERROR c"
		 		+ " WHERE c.location = a.idlocal";
//		 SELECT where status
		 String listbystatus = " SELECT id,location,errorname,localname,content,c.[status]"
		 		+ " FROM LOCATION a, REPORT_ERROR c"
		 		+ " WHERE c.location = a.idlocal"
		 		+ " AND status LIKE '%"+status+"%'";

			try {
//				connect to DB
				connect = UtilConnection.getConnection();
				state = connect.createStatement();
				
				if(connect!=null){
					
					 if (("".equals(status) || status==null)) {
			            	query = listall;
			            }else if((!"".equals(status) || status!=null)){
			            	query = listbystatus;
			            }
					 
//						execute query
						result = state.executeQuery(query);
						while(result.next()){
							Error error = new Error();
							error.setErrorID(result.getInt("id"));
							error.setLocation(result.getInt("location"));
							error.setErrorName(result.getString("errorname"));
							error.setLocalName(result.getString("localname"));
							error.setContent(result.getString("content"));
							error.setStatus(result.getInt("status"));
							list.add(error);
						}
					}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(connect!=null){
					connect.close();
				}
			}
//			return list error
			return list;		
		}
	 
// Add list into Database
	 
	 public boolean addError (Error error) throws SQLException {
			
		 String query = "INSERT INTO REPORT_ERROR(location,errorname,content,[status])"
					+ " VALUES(?,?,?,?)";
			
//		 connect to DB
			connect = UtilConnection.getConnection();
			if(connect != null){
					try {		
//					excute query
					pre = connect.prepareStatement(query);
					pre.setInt(1, error.getLocation());
					pre.setString(2, error.getErrorName());
					pre.setString(3, error.getContent());
					pre.setInt(4, error.getStatus());
					pre.execute();
					
					return true;
				} catch (SQLException e) {
					e.printStackTrace();
					return false;
				}finally{
					if(connect!=null){
						connect.close();
					}
				}
			}else 
				return false;
		}
	 
//	 get error information 
	 
	 public Error getErrorInfor(int id) throws SQLException{
		 
		 Error err = new Error();
		 String query = " SELECT id,location,errorname,localname,content,c.[status],a.addressDetail"
		 		+ " FROM LOCATION a, REPORT_ERROR c"
		 		+ " WHERE c.location = a.idlocal"
		 		+ " AND location = "+id+"";
//		 connect to DB
		 connect = UtilConnection.getConnection();
		 
		 if(connect!=null){
			 try {		

				state = connect.createStatement();
//				excute query
				result = state.executeQuery(query);
				while(result.next()){
					err.setErrorID(result.getInt("id"));
					err.setLocation(result.getInt("location"));
					err.setErrorName(result.getString("errorname"));
					err.setLocalName(result.getString("localname"));
					err.setContent(result.getString("content"));
					err.setStatus(result.getInt("status"));
					err.setAddressLocation(result.getString("addressDetail"));
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(connect!=null){
					connect.close();
				}
			}
		}
		return err;
	 }
}
